# coding=utf-8

'''
数据库操作模块的封装
Created on 2014年3月2日

@author: yuyue
'''
import MySQLdb

def batchCodes(values):
    sql = "insert into posts (post_title,post_excerpt,post_file_location,post_markdowncontent,post_content,location,post_status,post_category,tags) values (%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    batchinsert(sql,values)

def getLog():
    sql="select * from log"
    return query(sql)

def updateLog(name,page):
    sql="update log set name=%s, page=%s"
    values = []
    values.append(name)
    values.append(page)
    insert(sql,values)

def getCountriesPage():
    sql = "select * from post_category p where p.parent=1 and p.description != '' order by category_id desc limit 100 "
    return query(sql)
    
def  batchCountries(counties):
    i = 1
    sql = "insert into post_category(category_name,icon,description,parent,c_index) values (%s,%s,%s,%s,%s)"
    values = []
    
    for country in counties:
        value = (country['countryName'],country["countryImg"],country["countryHref"],1,1)
        values.append(value)
        print i
        if(i%50 == 0):
            batchinsert(sql,values) 
            values =[]
        
        if(len(counties) == i):
            batchinsert(sql,values)
            break
        i = i+1

def getConn():
    try:
        conn=MySQLdb.connect(host='localhost',user='root',passwd='root',db='swiftcodes',port=3306,charset='utf8')
        return conn
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])

def updateCountryPager(country):
    sql = "update post_category set pager=%s where category_id='%s'"  
    insert(sql,(country[7],country[0]))
    
def updateCountryType(country):
    sql = "update post_category set description='' where category_id='%s'"  
    insert(sql,country[0])
def delCodeByCountry(country):
    sql = "delete from posts where post_category='%s'" 
    insert(sql,country[0])
#sql values is tuple
def insert(sql,values):
    conn = getConn() 
    cur=conn.cursor()
    cur.execute(sql,values)
    conn.commit()
    cur.close()
    conn.close()

#sql values is list
def batchinsert(sql,values):
    conn = getConn() 
    cur=conn.cursor()
    cur.executemany(sql,values)
    conn.commit()
    cur.close()
    conn.close()

def query(sql):
    #sql = "select count(*) from post_category p where p.parent=1 and p.description != '' limit 20"
    conn = getConn() 
    cursor = conn.cursor()  
    count = cursor.execute(sql)
    print count
    results = cursor.fetchall() 
    cursor.close()  
    conn.close()
    return results
        
if __name__ == '__main__':
    getConn()